package com.house.java.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.house.java.dao.NewHouseDAO;
import com.house.java.model.NewHouse;
import com.house.java.model.Paging;
import com.house.java.util.DBUtil;

public class NewHouseDaoImpl implements NewHouseDAO {
	
	private static Logger log = Logger.getLogger(NewHouseDaoImpl.class);

	public List<NewHouse> queryNewHouseByCondition(Integer maxPrice, Integer minPrice,Paging paging,
			Integer wuyeCategory,Integer locationCountryCode,Integer locationProvinceCode,
			Integer locationCityCode,Integer locationDistrict,Long id){
		List<NewHouse> houses = new ArrayList<NewHouse>();
		Connection conn = DBUtil.getConnection();
		StringBuffer sb = new StringBuffer();
		sb.append("select * from new_house where 1=1 ");
		StringBuffer sbCount = new StringBuffer();
		sbCount.append("select count(*) from new_house where 1=1 ");
		int index = 0;
		if(minPrice != null){
			sb.append(" and avg_price >= ? ");
			sbCount.append(" and avg_price >= ? ");
			index++;
		}
		if(maxPrice != null){
			sb.append(" and avg_price <= ? ");
			sbCount.append(" and avg_price <= ? ");
			index++;
		}
		if(null != wuyeCategory){
			sb.append(" and wuye_category = ?");
			sbCount.append(" and wuye_category = ?");
			index++;
		}
		if(null != locationCountryCode){
			sb.append(" and country_code = ?");
			sbCount.append(" and country_code = ?");
			index++;
		}
		if(null != locationProvinceCode){
			sb.append(" and province_code = ?");
			sbCount.append(" and province_code = ?");
			index++;
		}
		if(null != locationCityCode){
			sb.append(" and city_code = ?");
			sbCount.append(" and city_code = ?");
			index++;
		}
		if(null != locationDistrict){
			sb.append(" and shangquan = ?");
			sbCount.append(" and shangquan = ?");
			index++;
		}
		if(null != id){
			sb.append(" and id = ?");
			sbCount.append(" and id = ?");
			index++;
		}
		if(paging != null){
			sb.append(" limit ?,?");
			index++;
			index++;
		}
		try {
			PreparedStatement pst = conn.prepareStatement(sb.toString());
			PreparedStatement pstCount = conn.prepareStatement(sbCount.toString());
			if(index > 0){
				int paramsIndex = 1;
				if(minPrice!=null){
					pst.setInt(paramsIndex, minPrice);
					pstCount.setInt(paramsIndex, minPrice);
					paramsIndex++;
				}
				if(maxPrice!=null){
					pst.setInt(paramsIndex, maxPrice);
					pstCount.setInt(paramsIndex, maxPrice);
					paramsIndex++;
				}
				if(null != wuyeCategory){
					pst.setInt(paramsIndex, wuyeCategory);
					pstCount.setInt(paramsIndex, wuyeCategory);
					paramsIndex++;
				}
				if(null != locationCountryCode){
					pst.setInt(paramsIndex, locationCountryCode);
					pstCount.setInt(paramsIndex, locationCountryCode);
					paramsIndex++;
				}
				if(null != locationProvinceCode){
					pst.setInt(paramsIndex, locationProvinceCode);
					pstCount.setInt(paramsIndex, locationProvinceCode);
					paramsIndex++;
				}
				if(null != locationCityCode){
					pst.setInt(paramsIndex, locationCityCode);
					pstCount.setInt(paramsIndex, locationCityCode);
					paramsIndex++;
				}
				if(null != locationDistrict){
					pst.setInt(paramsIndex, locationDistrict);
					pstCount.setInt(paramsIndex, locationDistrict);
					paramsIndex++;
				}
				if(null != id){
					pst.setLong(paramsIndex, id);
					pstCount.setLong(paramsIndex, id);
					index++;
				}
				if(paging != null){
					pst.setInt(paramsIndex++, (paging.getCurrentPage()-1)*paging.getPageSize());
					pst.setInt(paramsIndex++, paging.getPageSize());
					buildPaging(pstCount, paging);
				}
			}
			log.info(pst.toString());
			ResultSet result = pst.executeQuery();
			NewHouse house;
			while(result.next()){
				house = new NewHouse();
				house.setId(result.getLong("id"));
				house.setName(result.getString("loupan_name"));
				house.setDevelopers(result.getString("developers"));
				house.setPrice(result.getInt("avg_price"));
				house.setGreenRate(result.getDouble("green_rate"));
				house.setLocation(result.getString("location"));
				houses.add(house);
			}
			result.close();
			pst.close();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e.getMessage());
		}finally{
			try {
				conn.close();
			} catch (Exception e2) {
				logConnectionCloseExceptionFunction();
				log.error(e2.getMessage());
			}
		}
		return houses;
	}
	
	public List<NewHouse> queryNewHouseByCondition(Long id){
		List<NewHouse> houses = new ArrayList<NewHouse>();
		Connection conn = DBUtil.getConnection();
		StringBuffer sb = new StringBuffer();
		sb.append("select * from new_house where 1=1 ");
		StringBuffer sbCount = new StringBuffer();
		sbCount.append("select count(*) from new_house where 1=1 ");
		if(null != id){
			sb.append(" and id = ?");
			sbCount.append(" and id = ?");
		}
		try {
			PreparedStatement pst = conn.prepareStatement(sb.toString());
			PreparedStatement pstCount = conn.prepareStatement(sbCount.toString());
			if(null != id){
				pst.setLong(1, id);
				pstCount.setLong(1, id);
			}
			log.info(pst.toString());
			ResultSet result = pst.executeQuery();
			NewHouse house;
			while(result.next()){
				house = new NewHouse();
				house.setId(result.getLong("id"));
				house.setName(result.getString("loupan_name"));
				house.setWuyeCategory(result.getInt("wuye_category"));
				house.setProjectTrait(result.getString("xiangmu_trait"));
				house.setJianzhuCategory(result.getInt("jianzhu_category"));
				house.setZhuangxiuLocation(result.getInt("zhuangxiu_situation"));
				house.setLocation(result.getString("location"));
				house.setLocationCountryCode(result.getInt("country_code"));
				house.setLocationProvinceCode(result.getInt("province_code"));
				house.setLocationCityCode(result.getInt("city_code"));
				house.setLocationDistrict(result.getInt("shangquan"));
				house.setGreenRate(result.getDouble("green_rate"));
				house.setOpenTime(result.getDate("open_time"));
				house.setCompletionTime(result.getDate("completion_time"));
				house.setWuyeCharge(result.getInt("wuye_charge"));
				house.setWuyeCompany(result.getString("wuye_company"));
				house.setDevelopers(result.getString("developers"));
				house.setLicence(result.getString("licence"));
				house.setSellLocation(result.getString("sell_location"));
				house.setWuyeLocation(result.getString("wuye_location"));
				house.setTraffic(result.getString("traffic"));
				house.setPrice(result.getInt("avg_price"));
				house.setHouseSurroundId(result.getLong("surrounding_id"));
				house.setLoucengDesc(result.getString("loupan_total_floor"));
				house.setCarPortCount(result.getInt("carport_count"));
				house.setProDesc(result.getString("pro_desc"));
				house.setZhandiAcreage(result.getInt("zhandi_acreage"));
				house.setJianzhuAcreage(result.getInt("jianzhu_acreage"));
				house.setBeginTime(result.getDate("begin_time"));
				house.setOverTime(result.getDate("over_time"));
				house.setInvestor(result.getString("investor"));
				house.setAnjieBank(result.getString("anjie_bank"));
				house.setChanquanNianxian(result.getInt("chanquan_nianxian"));
				house.setHouseHolds(result.getInt("house_holds"));
				house.setImages(result.getString("images"));
				houses.add(house);
			}
			result.close();
			pst.close();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e.getMessage());
		}finally{
			try {
				conn.close();
			} catch (Exception e2) {
				logConnectionCloseExceptionFunction();
				log.error(e2.getMessage());
			}
		}
		return houses;
	}

	public void insertNewHouse(NewHouse house){
		Connection conn = DBUtil.getConnection();
		try {
			if(conn.getAutoCommit()){
				conn.setAutoCommit(false);
			}
		} catch (Exception e) {
			log.error("获取数据库连接是否自动提交发生异常");
		}
		try {
			String sql = "insert into new_house(" +
			"loupan_name,wuye_category,xiangmu_trait,jianzhu_category,zhuangxiu_situation," +
			"jianzhu_location,location,country_code,province_code,city_code," +
			"shangquan,green_rate,open_time,completion_time,wuye_charge," +
			"wuye_company,developers,licence,sell_location,wuye_location," +
			"traffic,avg_price,price_history_id,surrounding_id,loupan_total_floor," +
			"carport_count,pro_desc,zhandi_acreage,jianzhu_acreage,begin_time," +
			"over_time,investor,anjie_bank,chanquan_nianxian,house_holds," +
			"images) " +
			" values(? ,?,?,?,?,? ,?,?,?,?,? ,?,?,?,?,? ,?,?,?,?,? ,?,?,?,?,? ,?,?,?,?,? ,?,?,?,?,?)";
			PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			int parameterIndex = 1;
			pst.setString(parameterIndex++, house.getName());// 楼盘名字
			pst.setInt(parameterIndex++, house.getWuyeCategory());// 楼盘物业类别
			pst.setString(parameterIndex++, house.getProjectTrait());// 楼盘项目特色
			pst.setInt(parameterIndex++, house.getJianzhuCategory());// 楼盘建筑类别
			pst.setInt(parameterIndex++, house.getZhuangxiuLocation());// 楼盘装修状况
			
			pst.setString(parameterIndex++, house.getLocation());// 楼盘建筑的位置
			pst.setString(parameterIndex++, house.getLocation());// 楼盘建筑的位置
			pst.setInt(parameterIndex++, house.getLocationCountryCode());// 楼盘所属的国家的CODE
			pst.setInt(parameterIndex++, house.getLocationProvinceCode());// 楼盘所属的省份code
			pst.setInt(parameterIndex++, house.getLocationCityCode());// 楼盘所属的城市code
			
			pst.setInt(parameterIndex++, house.getLocationDistrict());// 楼盘所属的城市里的区code
			pst.setDouble(parameterIndex++, house.getGreenRate());// 楼盘的绿化率
			pst.setDate(parameterIndex++, house.getOpenTime());// 楼盘的开盘时间
			pst.setDate(parameterIndex++, house.getCompletionTime());// 楼盘的交房时间
			pst.setDouble(parameterIndex++, house.getWuyeCharge());// 物业费
			
			pst.setString(parameterIndex++, house.getWuyeCompany());// 物业公司
			pst.setString(parameterIndex++, house.getDevelopers());// 开发商
			pst.setString(parameterIndex++, house.getLicence());// 预售许可证
			pst.setString(parameterIndex++, house.getSellLocation());// 售楼地址
			pst.setString(parameterIndex++, house.getWuyeLocation());// 物业地址
			
			pst.setString(parameterIndex++, house.getTraffic());// 交通情况
			pst.setInt(parameterIndex++, house.getPrice());// 价格/均价
			pst.setLong(parameterIndex++, house.getHistoryPriceId());// 历史价格标ID
			pst.setLong(parameterIndex++, house.getHouseSurroundId());// 楼盘周围建筑信息的ID
			pst.setString(parameterIndex++, house.getLoucengDesc());// 楼层描述
			
			pst.setInt(parameterIndex++, house.getCarPortCount());// 停车位
			pst.setString(parameterIndex++, house.getProDesc());// 项目简介
			pst.setInt(parameterIndex++, house.getZhandiAcreage());// 占地面积
			pst.setInt(parameterIndex++, house.getJianzhuAcreage());// 建筑面积
			pst.setDate(parameterIndex++, house.getBeginTime());// 开工时间
			
			pst.setDate(parameterIndex++, house.getOverTime());// 竣工时间
			pst.setString(parameterIndex++, house.getInvestor());//投资商
			pst.setString(parameterIndex++,house.getAnjieBank());//按揭银行
			pst.setInt(parameterIndex++, house.getChanquanNianxian());//产权年限
			pst.setInt(parameterIndex++, house.getHouseHolds());// 户数

			pst.setString(parameterIndex++, house.getImages());// 楼盘图片存储的路径
			
			System.out.println("parameterIndex ---> "+parameterIndex);
			pst.executeUpdate();
			// 获取刚刚插入的记录的主键，设置到house对象中
			ResultSet resultSet = pst.getGeneratedKeys();
			if(resultSet.next()){
				Long id = resultSet.getLong(1);
				house.setId(id);
			}
			resultSet.close();
			pst.close();
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			
			
			log.error(e.getMessage());
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logConnectionRollbackException();
				log.error(Thread.currentThread().getId()+" "+e1.getMessage());
			}
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logConnectionCloseExceptionFunction();
				log.error(Thread.currentThread().getId()+" "+e.getMessage());
			}
		}
	}
	
	private static void logConnectionCloseExceptionFunction(){
		log.error(Thread.currentThread().getId()+" close Connection appear an Exception");
	}
	private static void logConnectionRollbackException(){
		log.error(Thread.currentThread().getId()+" rollback Connection appear an Exception");
	}
	
	/**
	 * 适用于  select count(*) 形式的SQL
	 * @param pst
	 * @param paging
	 */
	private void buildPaging(PreparedStatement pst,Paging paging){
		try {
			ResultSet rs = pst.executeQuery();
			paging.setTotalSize(rs.next()?rs.getInt(1):0);
			log.info(pst.toString());
			rs.close();
			pst.close();
		} catch (Exception e) {
			log.error(e.getMessage());
		}
	}

	public int deleteNewHouse(Long id) {
		int result = -1;
		Connection conn = DBUtil.getConnection();
		try {
			if(conn.getAutoCommit()){
				conn.setAutoCommit(false);
			}
		} catch (Exception e) {
			log.error("获取数据库连接是否自动提交发生异常");
		}
		try {
			String sql = "delete from new_house where id = ?";
			PreparedStatement pst = conn.prepareStatement(sql);
			int parameterIndex = 1;
			pst.setLong(parameterIndex, id);
			// 获取刚刚插入的记录的主键，设置到house对象中
			result = pst.executeUpdate();
			pst.close();
			conn.commit();
		} catch (Exception e) {
			log.error(e.getMessage());
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logConnectionRollbackException();
				log.error(Thread.currentThread().getId()+" "+e1.getMessage());
			}
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logConnectionCloseExceptionFunction();
				log.error(Thread.currentThread().getId()+" "+e.getMessage());
			}
		}
		return result;
	}


}
